package com.multitrack.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;

import com.google.android.maps.GeoPoint;
import com.multitrack.datatype.Event;
import com.multitrack.datatype.GPS;
import com.multitrack.map.datatype.GeoPoints;
import com.multitrack.map.datatype.Polyline;
import com.multitrack.map.util.Convert;

public class SQLite extends SQLiteOpenHelper {

	private static final String DB_NAME = "MultiTrack";

	public static final String TABLE_MEMBER = "Member";
	public static final String TABLE_EVENT = "Event";
	public static final String TABLE_ENTRANT = "Entrant";
	public static final String TABLE_TRACK = "Track";

	public static final String MEMBER_ID = "member_id";
	public static final String MEMBER_NAME = "member_name";

	public static final String EVENT_ID = "event_id";
	public static final String EVENT_NAME = "event_name";
	public static final String EVENT_PW = "event_pw";
	public static final String LEADER_ID = "leader_id";
	public static final String EVENT_LAT = "event_lat";
	public static final String EVENT_LON = "event_lon";

	public static final String TIME = "time";
	public static final String LAT = "lat";
	public static final String LON = "lon";
	public static final String ALT = "alt";
	public static final String SPEED = "speed";

	public final String TAG = "SQLite";

	String sql;
	SQLiteDatabase db;
	Cursor cursor;
	ContentValues cv;

	public SQLite(Context context) {
		super(context, DB_NAME, null, 1);

		sql = null;
		cv = new ContentValues();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		this.db = db;
		createTableMember();
		createTableEvent();
		createTableEntrant();
		createTableTrack();

	}

	private void createTableMember() {
		sql = "CREATE TABLE IF NOT EXISTS Member (" + "member_id VARCHAR(13), "
				+ "member_name VARCHAR(16) NULL, " + "PRIMARY KEY(member_id) "
				+ ");";
		db.execSQL(sql);
	}

	private void createTableEvent() {
		sql = "CREATE TABLE IF NOT EXISTS Event (" + "event_id CHARACTER(21), "
				+ "event_name VARCHAR(48), " + "event_pw VARCHAR(12) NULL, "
				+ "leader_id VARCHAR(13), " + "event_lat INTEGER NULL, "
				+ "event_lon INTEGER NULL, " + "PRIMARY KEY(event_id), "
				+ "FOREIGN KEY(leader_id) REFERENCES Member(member_id)" + ");";
		db.execSQL(sql);
	}

	private void createTableEntrant() {
		sql = "CREATE TABLE IF NOT EXISTS Entrant (" + "event_id VARCHAR(21), "
				+ "member_id VARCHAR(13), "
				+ "PRIMARY KEY (event_id, member_id), "
				+ "FOREIGN KEY (event_id) REFERENCES Event (event_id), "
				+ "FOREIGN KEY (member_id) REFERENCES Member (member_id)"
				+ ");";
		db.execSQL(sql);
	}

	private void createTableTrack() {
		sql = "CREATE TABLE IF NOT EXISTS Track ("
				+ "time LONG, "
				+ "event_id VARCHAR(21), "
				+ "member_id VARCHAR(13), "
				+ "lat DOUBLE, "
				+ "lon DOUBLE, "
				+ "alt DOUBLE, "
				+ "speed FLOAT NULL, "
				+ "PRIMARY KEY(time), "
				+ "FOREIGN KEY (event_id, member_id) REFERENCES Event (event_id, member_id) "
				+ ");";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO: When, Use DB upgrade new version.
	}

	public void dbTestTable() {
		Cursor tCursor = dbSelectAllMember();
		while (tCursor.moveToNext()) {
			String member_id = tCursor.getColumnName(0);
			String member_name = tCursor.getColumnName(1);
			System.out.println(member_id + "|" + member_name);
		}
		tCursor.close();

		tCursor = dbSelectAllTrack();
		while (tCursor.moveToNext()) {
			String time = tCursor.getColumnName(0);
			String event_id = tCursor.getColumnName(1);
			String member_id = tCursor.getColumnName(2);
			String lat = tCursor.getColumnName(3);
			String lon = tCursor.getColumnName(4);

			System.out.println(time + "|" + event_id + "|" + member_id + "|"
					+ lat + "|" + lon);
		}
	}

	public GeoPoints getTrack(String member_id) {
		// TABLE TRACK
		// (time(0), event_id(1), member_id(2), lat(3), lon(4), alt(5))
		GeoPoints points = new GeoPoints();
		Cursor cursor = dbSelectTrack(member_id);
		while (cursor.moveToNext()) {

			int tLat = cursor.getInt(3);
			int tLon = cursor.getInt(4);
			points.add(new GeoPoint(tLat, tLon));
		}

		return points;
	}

	private Cursor dbSelectMember(String member_id) {
		db = this.getReadableDatabase();

		sql = "SELECT * FROM Member WHERE member_id = '" + member_id + ";";
		Cursor result = db.rawQuery(sql, null);

		return result;
	}

	private Cursor dbSelectAllMember() {
		db = this.getReadableDatabase();

		sql = "SELECT * FROM Member;";
		Cursor result = db.rawQuery(sql, null);

		return result;
	}

	private Cursor dbSelectTrack(String member_id) {
		db = this.getReadableDatabase();

		sql = "SELECT * FROM Track WHERE member_id = '" + member_id + ";";
		Cursor result = db.rawQuery(sql, null);

		return result;
	}

	private Cursor dbSelectAllTrack() {
		db = this.getReadableDatabase();

		sql = "SELECT * FROM Track;";
		Cursor result = db.rawQuery(sql, null);

		return result;
	}

	public String dbGetMyId() {

		String result = null;
		db = this.getReadableDatabase();

		sql = "SELECT member_id FROM Member;";
		cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			result = cursor.getString(0);
		}

		return result;
	}

	public void dbSetMyId(String id) {
		db = this.getWritableDatabase();

		sql = "INSERT INTO Member " + "VALUES (" + "'" + id + "', NULL);";

		db.execSQL(sql);

	}

	public void dbSetMyName(String name) {
		db = this.getWritableDatabase();

		sql = "UPDATE Member " + "SET member_name = '" + name + "' "
				+ "WHERE member_name = NULL;";

		db.execSQL(sql);

	}

	public void dbInsertMember(String id, String name) {
		db = this.getWritableDatabase();

		sql = "INSERT INTO Member " + "VALUES (" + "'" + id + "', " + "'"
				+ name + "');";

		db.execSQL(sql);

	}

	public void dbInsertEvent(Event event) {
		db = this.getWritableDatabase();

		cv.clear();
		cv.put(EVENT_ID, event.getId());
		cv.put(EVENT_NAME, event.getName());
		cv.put(EVENT_PW, event.getPw());
		cv.put(LEADER_ID, event.getLeaderId());
		cv.put(EVENT_LAT, event.getLat());
		cv.put(EVENT_LON, event.getLon());

		db.insert(TABLE_EVENT, null, cv);

	}

	public void dbInsertEntrant(String event_id, String member_id) {
		db = this.getWritableDatabase();

		cv.clear();
		cv.put(EVENT_ID, event_id);
		cv.put(MEMBER_ID, member_id);

		db.insert(TABLE_ENTRANT, null, cv);
	}

	public void dbInsertTrack(String event_id, Location location) {
		db = this.getWritableDatabase();

		cv.clear();
		cv.put(TIME, location.getTime());
		cv.put(EVENT_ID, event_id);
		cv.put(MEMBER_ID, dbGetMyId());
		cv.put(LAT, location.getLatitude());
		cv.put(LON, location.getLongitude());
		cv.put(ALT, location.getAltitude());
		cv.put(SPEED, location.getSpeed());

		db.insert(TABLE_TRACK, EVENT_ID, cv);
	}

	public String dbGetCurrTime() {

		String currTime = null;

		db = this.getReadableDatabase();
		sql = "SELECT datetime();";
		cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			currTime = cursor.getString(0);
		}

		return currTime;
	}

	public boolean dbIsEvent(String event_id) {

		boolean isEvent;
		Cursor eventRow = dbSelectEvent(event_id);
		if (eventRow.moveToNext()) {
			isEvent = true;
		} else {
			isEvent = false;
		}
		return isEvent;
	}

	private Cursor dbSelectEvent(String event_id) {
		db = this.getReadableDatabase();
		sql = "SELECT * FROM Event WHERE event_id = '" + event_id + "';";
		Cursor result = db.rawQuery(sql, null);

		return result;
	}

	public void dbDeleteMember(String member_id) {
		db = this.getWritableDatabase();

		String whereClause = MEMBER_ID + "=?";
		String[] whereArgs = new String[] { member_id };
		if (db.delete(TABLE_MEMBER, whereClause, whereArgs) == 0) {
			Log.e(TAG, "DELETE ERROR - dbDeleteMember()");
		}
	}

	public void dbDeleteAllMember() {
		db = this.getWritableDatabase();

		sql = "DELETE FROM member;";
		db.execSQL(sql);

	}

	public void dbDeleteEvent(String event_id) {
		db = this.getWritableDatabase();

		String whereClause = EVENT_ID + " = ?";
		String[] whereArgs = new String[] { event_id };
		db.delete(TABLE_EVENT, whereClause, whereArgs);
	}

	public void dbDeleteEvent() {
		db = this.getWritableDatabase();

		String whereClause = EVENT_ID + " != ?";
		String[] whereArgs = new String[] { "myEvent" };
		db.delete(TABLE_EVENT, whereClause, whereArgs);
	}

	public void dbDeleteEntrant(String member_id) {
		db = this.getWritableDatabase();

		String whereClause = MEMBER_ID + "=?";
		String[] whereArgs = new String[] { member_id };

		if (db.delete(TABLE_ENTRANT, whereClause, whereArgs) == 0) {
			Log.e(TAG, "DELETE ERROR - dbDeleteEntrant()");
		}

	}

	public void dbDeleteTrack(String event_id) {
		db = this.getWritableDatabase();
		
		String whereClause = EVENT_ID + " = ? ";
		String[] whereArgs = new String[] { String.valueOf(event_id) };
		if (db.delete(TABLE_TRACK, whereClause, whereArgs) == 0) {
			Log.e(TAG, "DELETE ERROR - dbDeleteTrack()");
		}
	}
	
	public void dbDeleteTrack(double time) {
		db = this.getWritableDatabase();

		String whereClause = TIME + "=?";
		String[] whereArgs = new String[] { String.valueOf(time) };
		if (db.delete(TABLE_TRACK, whereClause, whereArgs) == 0) {
			Log.e(TAG, "DELETE ERROR - dbDeleteTrack()");
		}

	}

	public void dbUpdateEvent(Event event) {
		db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(EVENT_ID, event.getId());
		values.put(EVENT_NAME, event.getName());
		values.put(LEADER_ID, event.getLeaderId());
		values.put(EVENT_PW, event.getPw());
		values.put(EVENT_LAT, event.getLat());
		values.put(EVENT_LON, event.getLon());
		String whereClause = EVENT_ID + "=?";
		String[] whereArgs = new String[] { event.getId() };

		db.update(TABLE_EVENT, values, whereClause, whereArgs);
	}

	public Event dbSelectMyEvent() {
		// TODO Auto-generated method stub
		db = this.getReadableDatabase();

		sql = "SELECT * FROM Event WHERE event_name = 'myEvent' LIMIT 1;";

		Cursor row = db.rawQuery(sql, null);

		int event_id = row.getColumnIndex(EVENT_ID);
		int event_name = row.getColumnIndex(EVENT_NAME);
		int event_pw = row.getColumnIndex(EVENT_PW);
		int leader_id = row.getColumnIndex(LEADER_ID);
		int event_lat = row.getColumnIndex(EVENT_LAT);
		int event_lon = row.getColumnIndex(EVENT_LON);

		Event event = null;
		if (row.moveToNext()) {
			event = new Event();
			event.setId(row.getString(event_id));
			event.setName(row.getString(event_name));
			event.setPw(row.getString(event_pw));
			event.setLeaderId(row.getString(leader_id));
			event.setLat(row.getInt(event_lat));
			event.setLon(row.getInt(event_lon));
		}
		return event;
	}

	public GeoPoints dbGetMyTracks() {
		Cursor rows = dbSelectMyTrack();

		int lat = rows.getColumnIndex(LAT);
		int lon = rows.getColumnIndex(LON);

		GeoPoints points = new GeoPoints();

		while (rows.moveToNext()) {
			points.add(Convert.toGeoPoint(rows.getDouble(lat),
					rows.getDouble(lon)));
		}
		return points;
	}

	private Cursor dbSelectMyTrack() {
		db = this.getReadableDatabase();
		sql = "SELECT Track.* FROM Event, Track WHERE event_name = 'myEvent' ORDER BY time DESC;";
		return db.rawQuery(sql, null);
	}

}
